﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using PPI.Entities.Distribution;
using PPI.Entities.Enumerator;
using PPI.General;
using PPI.Mobile.Logic;
using System.Data;
using VMind.Global;

namespace PPI.Client.Logic.Distribution
{
    public class LMeter
    {
        public static List<EMeter> Save(List<EMeter> meters, Int32 poleId)
        {
            return WebConsumer.Distribution.WPole.Save(poleId, meters);
        }

        public static EMeter UpdateMeter(String connectionString, EDistributionPole pole, EMeter meter)
        {
            EMeter udpatedMeter = WebConsumer.Distribution.WPole.Update(pole.Id, meter);

            //Update meter image
            if (meter.Image != null)
            {
                if (meter.Image.FileName.ToUpper().StartsWith("UNSAVED"))
                {
                    FileInfo file = new FileInfo(meter.Image.FullName);
                    string _FileName = pole.Id + "_" + meter.Id + ".jpg";
                    string _FolderName = DirHelper.GetImageDirectory(pole.Id, meter.Id);

                    WebUploaderHelper _UploaderHelper = new WebUploaderHelper(Constants.K_WEBSITEUPLOADER);

                    if (file.Exists)
                    {
                        _UploaderHelper.Upload(_FolderName, _FileName, file,
                                               DatabaseTypes.DistributionInspection + "MeterImages");

                        string originalFileName = meter.Image.FileName;
                        meter.Image.FileName = _FileName;
                        LPole.SaveMeterImage(meter.Image, meter.Id);
                        meter.Image.FullName = string.Empty;
                    }
                }
                else
                {
                    LPole.SaveMeterImage(meter.Image, meter.Id);
                }
            }

            //Update meter on local database
            Update(connectionString, pole, meter, false);

            return udpatedMeter;
        }

        public static void UpdateUploadedData(List<EMeter> meters, Int32 poleId, string connectionString)
        {
            foreach (EMeter meter in meters)
            {
                StringBuilder _query = new StringBuilder();
                _query.AppendLine("UPDATE tb_Meters set MeterServerID = " + meter.ServerId + ", PoleServerID = " + poleId + ", UploadedDate = " +
                                  DateTime.Now.ToString("yyyy-MM-dd") + " where MeterId = " + meter.Id + " ");

                LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
            }
        }

        public static List<EMeter> GetPoleMeters(EDistributionPole distributionPole, string connectionString)
        {
            StringBuilder _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   MT.MeterServerID, ");
            _sql.AppendLine("   MT.MeterId, ");
            _sql.AppendLine("   MT.Latitude, ");
            _sql.AppendLine("   MT.Longitude, ");
            _sql.AppendLine("   MT.Number, ");
            _sql.AppendLine("   MT.ImageFileName, ");
            _sql.AppendLine("   MT.UploadedImage, ");
            _sql.AppendLine("   MT.Comments ");
            _sql.AppendLine("FROM tb_Meters MT ");
            _sql.AppendLine("WHERE MT.PoleFk = " + distributionPole.Id + ";");

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<EMeter> lstMeters = new List<EMeter>();

            foreach (DataRow dr in dt.Rows)
            {
                EMeter poleMeter = new EMeter();

                poleMeter.Id = ConvertHelper.ToInt32(dr["MeterId"]);
                poleMeter.ServerId = ConvertHelper.ToInt32(dr["MeterServerID"]);
                poleMeter.Latitude = ConvertHelper.Todouble(dr["Latitude"]);
                poleMeter.Longitude = ConvertHelper.Todouble(dr["Longitude"]);
                poleMeter.Number = dr["Number"].ToString();
                poleMeter.Image.Id = 0;
                poleMeter.Image.FileName = dr["ImageFileName"].ToString();

                string _imageDirectory = DirHelper.GetImagesDirectory(connectionString);
                poleMeter.Image.FullName = _imageDirectory + Path.DirectorySeparatorChar + poleMeter.Image.FileName;

                poleMeter.Image.ImageType = DistributionImageType.Meter;
                poleMeter.Comments = dr["Comments"].ToString();
                poleMeter.UploadedImage = ConvertHelper.Tobool(dr["UploadedImage"]);
                poleMeter.Notes = LMeterNote.GetMeterNotes(poleMeter, connectionString);

                lstMeters.Add(poleMeter);
            }

            return lstMeters;
        }

        public static List<EMeter> GetMeters(string connectionString)
        {
            StringBuilder _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   MT.MeterServerID, ");
            _sql.AppendLine("   MT.PoleServerID, ");
            _sql.AppendLine("   MT.MeterId, ");
            _sql.AppendLine("   MT.ImageFileName, ");
            _sql.AppendLine("   MT.UploadedImage ");
            _sql.AppendLine("FROM tb_Meters MT ");

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<EMeter> lstMeters = new List<EMeter>();

            foreach (DataRow dr in dt.Rows)
            {
                EMeter poleMeter = new EMeter();

                if (dr["MeterId"] != DBNull.Value)
                    poleMeter.Id = ConvertHelper.ToInt32(dr["MeterId"]);

                if (dr["MeterServerID"] != DBNull.Value)
                    poleMeter.ServerId = ConvertHelper.ToInt32(dr["MeterServerID"]);

                if (dr["PoleServerID"] != DBNull.Value)
                    poleMeter.PoleServerId = ConvertHelper.ToInt32(dr["PoleServerID"]);

                poleMeter.Image.Id = 0;

                if (dr["ImageFileName"] != DBNull.Value)
                    poleMeter.Image.FileName = dr["ImageFileName"].ToString();

                string _imageDirectory = DirHelper.GetImagesDirectory(connectionString);
                poleMeter.Image.FullName = _imageDirectory + Path.DirectorySeparatorChar + poleMeter.Image.FileName;

                poleMeter.Image.ImageType = DistributionImageType.Meter;

                if (dr["UploadedImage"] != DBNull.Value)
                    poleMeter.UploadedImage = ConvertHelper.Tobool(dr["UploadedImage"]);

                lstMeters.Add(poleMeter);
            }

            return lstMeters;
        }

        public static List<EMeter> GetProjectPoleMeters(EDistributionPole distributionPole, string connectionString)
        {
            StringBuilder _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   MT.MeterId, ");
            _sql.AppendLine("   MT.Latitude, ");
            _sql.AppendLine("   MT.Longitude, ");
            _sql.AppendLine("   MT.Number, ");
            _sql.AppendLine("   MT.ImageFileName, ");
            _sql.AppendLine("   MT.Comments ");
            _sql.AppendLine("FROM tb_Meters MT ");
            _sql.AppendLine("WHERE MT.PoleFk = " + distributionPole.Id + ";");

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<EMeter> lstMeters = new List<EMeter>();

            foreach (DataRow dr in dt.Rows)
            {
                EMeter poleMeter = new EMeter();

                poleMeter.Id = ConvertHelper.ToInt32(dr["MeterId"]);
                
                if (dr["Latitude"] != DBNull.Value)
                    poleMeter.Latitude = ConvertHelper.Todouble(dr["Latitude"]);

                if (dr["Longitude"] != DBNull.Value)
                    poleMeter.Longitude = ConvertHelper.Todouble(dr["Longitude"]); 
                
                poleMeter.Number = dr["Number"].ToString();
                poleMeter.Image.Id = 0;
                poleMeter.Image.FileName = dr["ImageFileName"].ToString();
                poleMeter.Image.ImageType = DistributionImageType.Meter;
                poleMeter.Comments = dr["Comments"].ToString();
                poleMeter.Notes = LMeterNote.GetProjectMeterNotes(poleMeter, connectionString);

                lstMeters.Add(poleMeter);
            }

            return lstMeters;
        }

        public static EMeter Update(string connectionString, EDistributionPole pole, EMeter meter, bool updateImage)
        {
            StringBuilder query = new StringBuilder();
            List<SqlParameter> listParameters = new List<SqlParameter>();

            try
            {
                #region - Pole UPDATE -

                query.AppendLine("UPDATE tb_Meters SET ");
                query.AppendLine("Latitude = @Latitude,");
                query.AppendLine("Longitude = @Longitude,");
                query.AppendLine("PoleFk = @PoleFk,");
                query.AppendLine("Number = @Number,");
                query.AppendLine("ImageFileName = @ImageFileName,");
                query.AppendLine("Comments = @Comments ");
                query.AppendLine("WHERE MeterId = @MeterId");

                listParameters.Add(DBHelper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, meter.Id));

                if (meter.Latitude != Double.MinValue)
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, meter.Latitude));
                else
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (meter.Longitude != Double.MinValue)
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, meter.Longitude));
                else
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                listParameters.Add(DBHelper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, pole.Id));

                if (meter.Number != string.Empty)
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Number", 25, ParameterDirection.Input, false, meter.Number));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Number", 25, ParameterDirection.Input, false, DBNull.Value));

                if (meter.Image != null)
                {
                    if (updateImage)
                    {
                        meter.Image.GenerateNewName(meter.Id.ToString());
                        SaveImage(meter.Image, connectionString);
                    }
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@ImageFileName", 250, ParameterDirection.Input, false, meter.Image.FileName));
                }
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@ImageFileName", 250, ParameterDirection.Input, false, DBNull.Value));

                if (meter.Comments != string.Empty)
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, meter.Comments));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, DBNull.Value));

                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion

                #region - Notes UPDATE -

                query = new StringBuilder("DELETE tb_NotesXMeters WHERE MeterFk = @MeterId");
                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, meter.Id));
                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                query = new StringBuilder("SELECT CAST(ISNULL(MAX(NoteXMeterId), 1) AS int) AS id FROM tb_NotesXMeters");
                int tableLastId = (int)LMobileDatabase.ExecuteScalar(query.ToString(), connectionString);
                int cont = 0;

                foreach (ENote note in meter.Notes)
                {
                    query = new StringBuilder("INSERT INTO tb_NotesXMeters (NoteXMeterId, MeterFk, NoteFk) VALUES (");
                    query.AppendLine("@NoteXMeterId, @MeterFk, @NoteFk)");

                    listParameters.Clear();
                    cont++;
                    listParameters.Add(DBHelper.CreateIntParameter("@NoteXMeterId", ParameterDirection.Input, false, tableLastId + cont));
                    listParameters.Add(DBHelper.CreateIntParameter("@MeterFk", ParameterDirection.Input, false, meter.Id));
                    listParameters.Add(DBHelper.CreateIntParameter("@NoteFk", ParameterDirection.Input, false, note.Id));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
                }

                #endregion

                return meter;
            }
            catch (Exception exc)
            {
                throw exc;
            }
        }

        public static void Delete(string connectionString, EMeter meter)
        {
            List<SqlParameter> listParameters = new List<SqlParameter>(); 
            StringBuilder query = new StringBuilder();

            try
            {
                #region - Meter DELETE -

                query.AppendLine("DELETE tb_Meters ");
                query.AppendLine("WHERE MeterId = @MeterId ");

                listParameters.Add(DBHelper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, meter.Id));

                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion

                #region - Notes DELETE -

                foreach (ENote note in meter.Notes)
                {
                    query = new StringBuilder("DELETE tb_NotesXMeters ");
                    query.AppendLine("WHERE NoteFk = @NoteFk AND MeterFk = @MeterId");

                    listParameters.Clear();
                    listParameters.Add(DBHelper.CreateIntParameter("@NoteFk", ParameterDirection.Input, false, note.Id));
                    listParameters.Add(DBHelper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, meter.Id));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
                }

                #endregion
            }
            catch (Exception exc)
            {
                throw exc;
            }
        }


        /// <summary>
        /// Deletes from server
        /// </summary>
        /// <param name="meter"></param>
        public static void Delete(EMeter meter)
        {
            WebConsumer.Distribution.WPole.DeleteMeter(meter.Id);
        }


        public static Int32 SaveImage(EDistributionPoleImage image, string connectionString)
        {
            Int32 returnId = image.Id;

            if (image.Id == Int32.MinValue)
            {
                string newImageName = image.FileName;
                DirHelper.CopyImageToOutbox(connectionString, image.FullName, newImageName);
            }

            return returnId;
        }

        public static List<EMeter> SelectAllWithoutImage()
        {
            DataTable dt = WebConsumer.Distribution.WPole.GetMetersWithoutImage().Tables[0];
            List<EMeter> lstMeters = new List<EMeter>();

            foreach (DataRow drMeter in dt.Rows)
            {
                EMeter meter = new EMeter();

                meter.ServerId = ConvertHelper.ToInt32(drMeter["Vendor_Distribution_Meters_MeterId"]);
                meter.PoleServerId = ConvertHelper.ToInt32(drMeter["Vendor_Distribution_PoleFk"].ToString());

                lstMeters.Add(meter);
            }

            return lstMeters;
        }
    }
}
